package DBLayer;
import ModelLayer.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DBOrder {

    private Connection con;

    // Constructor
    public DBOrder(){
        con = DbConnection1.getInstance().getDBcon();
    }

    public ArrayList<Order> getAllOrders(boolean retrieveAssociation)
    {
        return miscWhere("", retrieveAssociation);
    }


public Order findOrderId(String idOrder, boolean retrieveAssociation){
	String wClause = " idOrder = '" + idOrder + "'";
	return singleWhere(wClause, retrieveAssociation);
}

public int insertOrder(Order order) throws Exception{
	int nextIdOrder = GetMax.getMaxId("Select max(idOrder) from [order]");
	nextIdOrder = nextIdOrder + 1;
	System.out.println("next idOrder = " + nextIdOrder);

	int rc = -1;
        
	String query = "INSERT INTO [Order](idOrder, idOffer, createdDate,"+ 
		" paymentDeadline, statusOrder) VALUES('" +
        	nextIdOrder + "','" +
        	order.getOffer().getIdOffer() + "','" +
        	order.getCreatedDate() + "','" +
        	order.getPaymentDeadline() + "','" +
        	order.getStatusOrder() + "')";

        System.out.println("insert : " + query);
        try {
        	Statement stmt = con.createStatement();
       		stmt.setQueryTimeout(5);
        	rc = stmt.executeUpdate(query);
        	stmt.close();
        }
        catch (SQLException ex){
        	System.out.println("Order not created");
        	throw new Exception("Order is not inserted correctly");
        }
        return rc;
    }


    public int updateOrder(Order order){
        Order orderObj = order;
        int rc =-1;

        String query = "UPDATE [order] WHERE idOrder = '" + orderObj.getIdOrder() +  "' SET " +
                "createdDate ='" + orderObj.getCreatedDate() + "', " +
                "paymentDeadline ='" + orderObj.getPaymentDeadline() + "', " +
                "statusOrder ='" + orderObj.getStatusOrder() + "'";
        System.out.println("Update query:" + query);

        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);

            stmt.close();
        }
        catch(Exception ex){
            System.out.println("Update exception in order db: "+ex);
        }
        return(rc);
    }


    public int deleteOrder(String idOrder){
        int rc=-1;

        String query = "DELETE FROM [order] WHERE idOrder = '" +
                idOrder + "'";
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception ex){
            System.out.println("Delete exception in order db: "+ex);
        }
        return(rc);
    }



    /**
     * Implementation of the private methods used in the above public methods is from here and downwards.
     */

    private ArrayList<Order> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Order> list = new ArrayList<Order>();

        String query = buildQuery(wClause);

        try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()){
                Order orderObj = new Order();
                orderObj = buildOrder(results);
                list.add(orderObj);
            }
                stmt.close();

                /*
                if (retrieveAssociation){
                    for (Order orderObj : list){

                    }
                }
                */
        }
        catch (Exception e){
            System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return list;
    }

    private Order singleWhere(String wClause, boolean retrieveAssociation){
        ResultSet results;
        Order orderObj = new Order();

        String query = buildQuery(wClause);
        System.out.println(query);

        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next()){
                orderObj = buildOrder(results);

                stmt.close();
                /*
                if (retrieveAssociation)
                {

                }
                */
            }
        }
        catch(Exception e){
            System.out.println("Query exception: " + e);
        }
        return orderObj;
    }

    private String buildQuery(String wClause){
        String query="SELECT idOrder, idOffer, createdDate, paymentDeadline, statusOrder FROM [order]";

        if (wClause.length() > 0)
            query = query+" WHERE "+wClause;

        return query;
    }

    private Order buildOrder(ResultSet results){
        Order orderObj = new Order();
        try{
            orderObj.setIdOrder(results.getString("idOrder"));
            orderObj.setOffer(new Offer(results.getString("idOffer")));
            orderObj.setCreatedDate(results.getString("createdDate"));
            orderObj.setPaymentDeadline(results.getString("paymentDeadline"));
            orderObj.setStatusOrder(results.getString("statusOrder"));
        }
        catch (Exception e){
            System.out.println("error is building the employee object");
        }
        return orderObj;

    }

}
